Calculate the number of children regardless of where the children live

In microdata.no there are ready-made variables that show the number of children in the family or household, but this only includes the children who actually live with the respective parents.

In some cases, it can be useful to also include children who live elsewhere than with their parents. This includes children who live with only one of the parents where the parents do not live together, or where the child does not live with either parent.

The script below shows how to find parents with children under 18 regardless of whether the child lives with the mother and/or father or elsewhere. Furthermore, we calculate the number of children per parent, and also limit to parents living in Oslo.

 require no.ssb.fdb:30 as db

// Create dataset of children under 18
create-dataset children
import db/BEFOLKNING_FOEDSELS_AAR_MND as birthdate
generate age = 2022 - int(birthdate/100)
keep if age < 18
import db/BEFOLKNING_FAR_FNR as fatherid
import db/BEFOLKNING_MOR_FNR as motherid

// Create dataset of parents living in Oslo
create-dataset osloparents
import db/BOSATTEFDT_BOSTED 2022-12-31 as municipality_father
keep if municipality_father == '0301'
clone-variables municipality_father -> municipality_mother
merge municipality_father into children on fatherid
merge municipality_mother into children on motherid

// Create dummy variables for children indicating whether they have a father or mother living in Oslo, and remove those who do not have any parents living in Oslo
use children
generate oslomother = municipality_mother == '0301'
generate oslofather = municipality_father == '0301'
drop if !oslofather & !oslomother
tabulate oslofather oslomother

// Create a copy of the children dataset to be able to link both mother and father with the child
clone-dataset children children2

// Aggregate the children dataset by counting the number of children per father living in Oslo, and link it to the parent dataset
collapse(count) oslofather -> num_children_father, by(fatherid)
merge num_children_father into osloparents

// Use the copied dataset to do the same with mothers living in Oslo
use children2
collapse(count) oslomother -> num_children_mother, by(motherid)
merge num_children_mother into osloparents

// Use the parent dataset to create statistics on the number of children. Remove parents who do not have children from the population
use osloparents
keep if num_children_father > 0 | num_children_mother > 0

textblock
Create a control table that shows the number of children for mother and father living in Oslo. Men will have missing on the variable num_children_mother, and women will have missing on the variable num_children_father. This can be seen from the row/column showing SYSMISS. A few individuals have a value on both num_children_father and num_children_mother. This can be due to people changing gender, or it may be a coding error:
endblock
tabulate num_children_father num_children_mother, missing

// Replace missing values with zero so that you can sum the number of children over num_children_father and num_children_mother. With a few exceptions, everyone has a value only on one of the variables. The variable num_children should then in practice show the number of children per parent living in Oslo (regardless of where the child lives). Note that you are counting the number of children per parent and not per family. 

replace num_children_father = 0 if sysmiss(num_children_father)
replace num_children_mother = 0 if sysmiss(num_children_mother)
generate num_children = num_children_father + num_children_mother

textblock
Number of parents living in Oslo distributed by number of children under 18:
endblock
tabulate num_children